﻿Public Class Abm_temas
    Dim conexion_bd As String
    Enum estado
        insertar
        modificar
    End Enum
    Enum termino
        aprobado
        rechazado
    End Enum
    Dim accion As estado = estado.insertar

    Private Sub Temas_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'conexión a la base de datos'
        Me.conexion_bd = "Provider=SQLOLEDB;Data Source=GTºN1\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=C:\DB\LIBRERIA_MAGOO.MDF"

        'carga la lista
        carga_lista(Me.leer_tabla())

        Me.btn_nuevo.Focus()


    End Sub

    Private Sub btn_quitar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_quitar.Click
        Me.quitar()
    End Sub

    Private Sub lst_temas_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lst_temas.Click
        Dim nombre As String = sender.displaymember

        Dim conexion As New Data.OleDb.OleDbConnection
        Dim command As New Data.OleDb.OleDbCommand
        Dim tabla As New Data.DataTable

        conexion.ConnectionString = Me.conexion_bd
        conexion.Open()

        command.Connection = conexion
        command.CommandType = CommandType.Text
        command.CommandText = " select * from Temas where Descripcion =" & nombre

        tabla.Load(command.ExecuteReader())


        Me.txt_nombre.Text = Me.lst_temas.Text

        Me.txt_nombre.Enabled = True

        Me.txt_nombre.Focus()

        Me.accion = estado.modificar
        conexion.Close()

        Me.btn_quitar.Enabled = True
        Me.btn_guardar.Enabled = True
    End Sub

    Private Sub btn_guardar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_guardar.Click
        If Me.validar() = True Then
            If Me.accion = estado.insertar Then
                If Me.validar_existencia() = termino.aprobado Then 'valida si existe otro igual
                    Me.nuevo()
                    MessageBox.Show("Tema agregado correctamente", "Importante", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Else
                    MsgBox("Ya existe un tema con el nombre ingresado", MsgBoxStyle.Critical, "Importante")
                    Exit Sub
                End If
            Else
                Me.modificar()
                MessageBox.Show("Tema modificado correctamente", "Importante", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If
            carga_lista(Me.leer_tabla())

            Me.txt_nombre.Text = ""
            Me.btn_nuevo.Focus()
            Me.btn_guardar.Enabled = False
            Me.btn_quitar.Enabled = False
        End If
        Me.txt_nombre.Enabled = False

    End Sub

    Private Sub btn_nuevo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_nuevo.Click
        Me.txt_nombre.Enabled = True
        Me.btn_guardar.Enabled = True
        Me.btn_quitar.Enabled = True

        Me.txt_nombre.Text = ""
        Me.txt_nombre.Focus()
        Me.accion = estado.insertar
    End Sub

    Private Function nuevo() As termino
        Dim conexion As New Data.OleDb.OleDbConnection
        Dim command As New Data.OleDb.OleDbCommand
        Dim consulta As String = "" 'declaro la variable consulta, vacia'


        'abro conexion'
        conexion.ConnectionString = Me.conexion_bd
        conexion.Open()

        'consulta de nuevo'
        consulta = "insert into Temas(Descripcion) values('" & Me.txt_nombre.Text & "')"

        command.CommandType = CommandType.Text
        command.CommandText = consulta
        command.Connection = conexion
        command.ExecuteNonQuery()
        conexion.Close()
        Return termino.aprobado
    End Function
    Private Function modificar() As termino
        Dim conexion As New Data.OleDb.OleDbConnection
        Dim command As New Data.OleDb.OleDbCommand
        Dim consulta As String = "" 'declaro la variable consulta, vacia'

        'abro conexion'
        conexion.ConnectionString = Me.conexion_bd
        conexion.Open()

        'consulta de nuevo'
        consulta = "Update Temas Set Descripcion='" & Me.txt_nombre.Text & "' where IdTema=" & Me.lst_temas.SelectedValue

        command.CommandType = CommandType.Text
        command.CommandText = consulta
        command.Connection = conexion
        command.ExecuteNonQuery()
        conexion.Close()
        Return termino.aprobado


    End Function
    Private Function quitar()
        Dim conexion As New Data.OleDb.OleDbConnection
        Dim command As New Data.OleDb.OleDbCommand
        If MessageBox.Show("¿Esta seguro que desea borrar el tema seleccionado?", "Importante", _
               MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then

            'abro conexion'
            conexion.ConnectionString = Me.conexion_bd
            conexion.Open()

            command.Connection = conexion
            command.CommandType = CommandType.Text
            command.CommandText = "delete from Temas where Descripcion= '" & Me.txt_nombre.Text & "'"
            command.ExecuteNonQuery()
            conexion.Close()

            carga_lista(Me.leer_tabla())

            Me.txt_nombre.Text = ""
            Me.btn_quitar.Enabled = False
        End If
        Return termino.aprobado
    End Function
    Private Function leer_tabla() As Data.DataTable
        Dim conexion As New Data.OleDb.OleDbConnection
        Dim command As New Data.OleDb.OleDbCommand
        Dim tabla As New Data.DataTable 'declaro la variable tabla'

        'abro conexion
        conexion.ConnectionString = Me.conexion_bd
        conexion.Open()

        command.Connection = conexion
        command.CommandType = CommandType.Text
        command.CommandText = "select * from Temas"

        tabla.Load(command.ExecuteReader())
        conexion.Close()
        Return tabla
    End Function

    Private Sub carga_lista(ByVal tabla As Data.DataTable)
        Me.lst_temas.DataSource = tabla
        Me.lst_temas.ValueMember = "IdTema"
        Me.lst_temas.DisplayMember = "Descripcion"
    End Sub

    Private Function validar() As Boolean
        If Me.txt_nombre.Text = "" Then
            MsgBox("Debe completar el nombre", MsgBoxStyle.Critical, "Importante")
            Me.txt_nombre.Focus()
            Return False
        End If
        If (IsNumeric(Me.txt_nombre.Text)) Then
            MsgBox("El nombre debe ser texto", MsgBoxStyle.Critical, "Importante")
            Me.txt_nombre.Focus()
            Return False
        End If

        Return True

    End Function
    Private Function validar_existencia() As termino
        Dim conexion As New Data.OleDb.OleDbConnection
        Dim command As New Data.OleDb.OleDbCommand
        Dim tabla As New Data.DataTable
        Dim consulta As String = ""

        conexion.ConnectionString = Me.conexion_bd
        conexion.Open()

        consulta = "select * from Temas where Descripcion = '" & Me.txt_nombre.Text & "'"

        command.CommandType = CommandType.Text
        command.CommandText = consulta
        command.Connection = conexion

        tabla.Load(command.ExecuteReader())

        conexion.Close()

        If tabla.Rows.Count() = 1 Then
            Return termino.rechazado
        Else
            Return termino.aprobado
        End If


    End Function

    Private Sub btn_salir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_salir.Click
        If System.Windows.Forms.DialogResult.OK = MessageBox.Show("Cerrar esta ventana?", "Importante", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) Then
            Me.Close()
        End If
    End Sub

    Private Sub btn_buscar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_buscar.Click
        If validar_campos_de_busqueda() Then
            Try
                Dim conexion As New AccesoADatos(conexion_bd)
                carga_lista(conexion.leerTabla("*", "Temas", "Descripcion like '%" & Me.txt_nombre.Text & "%'"))
                btn_guardar.Enabled = False
            Catch ex As OleDb.OleDbException
                MessageBox.Show("Debe completar los campos correctamente", "Error de búsqueda", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                txt_nombre.Clear()
                carga_lista(Me.leer_tabla())
            End Try
        Else
            MessageBox.Show("Debe completar todos los campos", "Error en la búsqueda", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        End If
    End Sub

    Private Function validar_campos_de_busqueda() As Boolean
        Dim validacion As Boolean = True
        If txt_nombre.Text = "" Then
            validacion = False
        End If
        Return validacion
    End Function
End Class